JChem Cartridge 5.4 -> 6.1 migration

Hello!

While performing migration from JChem cartridge from v5.4.0.1 to v6.4.1.0 using "upgrade" script I got 2 kind of errors:

1) Exceptions on molecule update

Error at molecule with cd_id: 1
Error at molecule with cd_id: 2
java.sql.SQLException: ORA-29877: failed in the execution of the ODCIINDEXUPDATE routine
ORA-29540: class chemaxon/jchem/cartridge/oraresident/JCFunctionsBlob does not exist
ORA-06512: at "JCHEM.JCHEM_BLOB_PKG", line 115
ORA-06512: at "JCHEM.JC_IDXTYPE_IM", line 736
        at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:70)
        at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:133)
        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:206)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:455)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:413)
        at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:1034)
        at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:194)
        at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:953)
        at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1222)
        at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3387)
        at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3468)
        at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate(OraclePreparedStatementWrapper.java:1350)
        at chemaxon.jchem.base.persist.impl.sql.modification.SqlModificationStatement.execute(SqlModificationStatement.java:63)
        at chemaxon.jchem.base.persist.impl.sql.SqlStructureCollectionImpl.handleRecord(SqlStructureCollectionImpl.java:302)
        at chemaxon.jchem.base.persist.update.StructureCollectionHandler.updateRecord(StructureCollectionHandler.java:102)
        at chemaxon.jchem.db.UpdateHandler.execute(UpdateHandler.java:1066)
        at chemaxon.jchem.db.UpdateHandler.execute(UpdateHandler.java:1001)
        at chemaxon.jchem.db.Regenerator$RegenThread.process(Regenerator.java:165)
        at chemaxon.jchem.db.Regenerator$RegenThread.run(Regenerator.java:124)
java.sql.SQLException: ORA-29877: failed in the execution of the ODCIINDEXUPDATE routine
ORA-29540: class chemaxon/jchem/cartridge/oraresident/JCFunctionsBlob does not exist
ORA-06512: at "JCHEM.JCHEM_BLOB_PKG", line 115
ORA-06512: at "JCHEM.JC_IDXTYPE_IM", line 736
        at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:70)
        at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:133)
        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:206)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:455)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:413)
        at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:1034)
        at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:194)
        at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:953)
        at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1222)
        at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3387)
        at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3468)
        at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate(OraclePreparedStatementWrapper.java:1350)
        at chemaxon.jchem.base.persist.impl.sql.modification.SqlModificationStatement.execute(SqlModificationStatement.java:63)
        at chemaxon.jchem.base.persist.impl.sql.SqlStructureCollectionImpl.handleRecord(SqlStructureCollectionImpl.java:302)
        at chemaxon.jchem.base.persist.update.StructureCollectionHandler.updateRecord(StructureCollectionHandler.java:102)
        at chemaxon.jchem.db.UpdateHandler.execute(UpdateHandler.java:1066)
        at chemaxon.jchem.db.UpdateHandler.execute(UpdateHandler.java:1001)
        at chemaxon.jchem.db.Regenerator$RegenThread.process(Regenerator.java:165)
        at chemaxon.jchem.db.Regenerator$RegenThread.run(Regenerator.java:124)

2) Script couldn't install cartridge service on Windows

Do you want to upgrade JChemServer as an operating system service? (y, n) [y]: n

Do you want to install a new Windows Service for JChem Server as JChemCartridgeService? (y, n) [n]: y
INFO: Executing "D:\ChemAxon\jchem\cartridge\prunsrv" "//IS//JChemCartridgeService" "--DisplayName=null" "--Install=D:\ChemAx
on\jchem\cartridge\prunsrv" "--JavaHome=D:\Java\jdk1.7.0_05\jre" "--Jvm=D:\Java\jdk1.7.0_05\jre\bin\server\jvm.dll" "--JvmOpt
ions=-Xrs#-Dchemaxon.jchem.cartridge.config.file=D:\ChemAxon\jchem\cartridge\conf\jcart.properties" "--Classpath=D:\ChemAxon\
jchem\lib\jchem.jar" "--StartMode=jvm" "--StopMode=jvm" "--StartClass=chemaxon.jchem.cartridge.server.Bootstrapper" "--StartP
arams=start" "--StartPath=D:\ChemAxon\jchem\cartridge" "--StopClass=chemaxon.jchem.cartridge.server.Bootstrapper" "--StopPara
ms=stop" "--StopPath=D:\ChemAxon\jchem\cartridge" "--StdOutput=D:\ChemAxon\jchem\cartridge\logs\server.stdout" "--StdError=D:
\ChemAxon\jchem\cartridge\logs\server.stderr"
SEVERE: Exit code: 8, expected: 0
SEVERE: error
java.lang.RuntimeException:
        at chemaxon.util.RuntimeUtils.exec(RuntimeUtils.java:281)
        at chemaxon.util.RuntimeUtils.exec(RuntimeUtils.java:263)
        at chemaxon.jchem.cartridge.install.WindowsServiceInstaller.execCommand(WindowsServiceInstaller.java:183)
        at chemaxon.jchem.cartridge.install.WindowsServiceInstaller.installOrUpdateService(WindowsServiceInstaller.java:103)
        at chemaxon.jchem.cartridge.install.WindowsServiceInstaller.installService(WindowsServiceInstaller.java:38)
        at chemaxon.jchem.cartridge.install.UpgradeCmdLine.createNewJChemServiceMaybe(UpgradeCmdLine.java:145)
        at chemaxon.jchem.cartridge.install.UpgradeCmdLine.upgradeJChemServerService(UpgradeCmdLine.java:126)
        at chemaxon.jchem.cartridge.install.UpgradeCmdLine.upgrade(UpgradeCmdLine.java:73)
        at chemaxon.jchem.cartridge.install.UpgradeCmdLine.main(UpgradeCmdLine.java:783)

Can errors of the 1st kind be ignored and is it safe to use JChem Cartridge v6.4.1 after an upgrade with such errors? Is it an issue with service installation during upgrade process in current JChem version?

Thank you.

Hi,

The second type of error is only about the service upgrade, but the first type of errors are more important. It means that the indexes were not upgraded correctly. I suggest rebuilding them with the following script:

alter index <index-name> rebuild;

It is a slow process for each table, but its necessary to use the index with the new version.

Regards,

Tamas

Hello Tamas,

    Thank you for your answer.

    Are there any steps that can help to avoid these errors on other JChem v5.4 instances? Like migration via intermediate JChem version (e.g., 5.4 -> 5.x -> 6.1) or something else? Can necessary indices be identified and rebuilt using a single SQL script? What to do with service upgrade, why is it failing when it seems to be a simple operation? Can a service be installed with a different command after running the "upgrade" script?

Thank you.

Hello,

We are running tests continuously, which upgrade from 5.4 to the latest version and we haven't received these error messages, so I can't tell yet, what is the exact cause of this error. To find out, please send us the logs from the jchem/cartridge/logs directory. The name of the file is jcc-upgrade?.log. You can attach it to your post or sned use directly to jccartridge-support at chemaxon dot com.

Theres is a question during upgrade about the upgrade of old tables. You can select upgrade all, none or ask for each table. If theupgrade is succesfull and you select all, all tables will be rebuilt at the end of the upgrade (the same command runs for all tables that I copied in earlier). The upgrade of the tables can be very slow, in  fact, after so much difference between the table versions, everything has to be dropped and recreated in the background, so it runs with the same speed as a drop index and a create index command. If you select none or ask for each table, the script to upgrade the tables will be shown on the screen (standard output) at the end of the upgrade. Unfortunately in your case, there was an error, which prevented these information to be seen on the screen.

About the windows service, we received an error code 8 from Windows, which is the error code for "Not enough memory". It is a very confusing error message, because it can also mean that the disk is full, an important file is missing, etc. So it's very difficult to determine the cause of this error. We don't have a command-line utility to install or upgrade only the service. However, there is an info message in the log, that you can run to upgrade it, just in the code that you copied in. You can run this from the command line anytime:

"D:\ChemAxon\jchem\cartridge\prunsrv" "//IS//JChemCartridgeService" "--DisplayName=null" "--Install=D:\ChemAx
on\jchem\cartridge\prunsrv" "--JavaHome=D:\Java\jdk1.7.0_05\jre" "--Jvm=D:\Java\jdk1.7.0_05\jre\bin\server\jvm.dll" "--JvmOpt
ions=-Xrs#-Dchemaxon.jchem.cartridge.config.file=D:\ChemAxon\jchem\cartridge\conf\jcart.properties" "--Classpath=D:\ChemAxon\
jchem\lib\jchem.jar" "--StartMode=jvm" "--StopMode=jvm" "--StartClass=chemaxon.jchem.cartridge.server.Bootstrapper" "--StartP
arams=start" "--StartPath=D:\ChemAxon\jchem\cartridge" "--StopClass=chemaxon.jchem.cartridge.server.Bootstrapper" "--StopPara
ms=stop" "--StopPath=D:\ChemAxon\jchem\cartridge" "--StdOutput=D:\ChemAxon\jchem\cartridge\logs\server.stdout" "--StdError=D:
\ChemAxon\jchem\cartridge\logs\server.stderr"

 

Best regards,

Tamas

Hello Tamas,

    I could locate "jcc-upgrade.log" file neither anywhere in JChem 6.1 folders nor in JChem 5.4 folders, but there was a pretty big "jcart0.log" file in JChem 6.1/cartridge folder. I sent it to the support email as you asked. Strangely this log doesn't contain records about JCFunctionsBlob class errors.

    Thank you for the tip with the command to install the cartridge service, I'll try it.

Hi,

Thank you for the logs, it was the file we needed, I missed the name of the file, because we renamed it later, starting from the 6..2 version, sorry.

We couldn't find something very trivial, but we try to reproduce it soon, and try to find out, what is the cause of the error. But we could figure out that the problem is indeed with table regeneration, which can be done manually, with the alter index commands I mentioned before. If you select "none" for the question during upgrade to "Rebuild indices?", than you get a list on the screen of post-install tasks which contains these alter index commands.And right after the install you can run them manually.

Regards,

Tamas

Hi,

We tried to reproduce the error you received in different environments, but unfortunately we couldn't do it. To avoid this kind of error we suggest choosing not to upgrade the table during the upgrade of the cartridge, but do it after the upgrade has been completed.

As a consequence of your post we added some more logging to our software to get more information about these kind of failures and so we can detect the exact cause and making troubleshooting easier.

Best regards,

Tamas

Hello Tamas,

    Thank you for your effort in investigation the issues. I tried to do the upgrade once more with automatic table regeneration and received the same kind of errors. I'll try to select "none" option and check the results.

    I also tried to manually install a Windows service with the command outputted by upgrade utility and the error code was 8 again. It seems the problem is with "--DisplayName=null" option: when I changed it to a more meaningful string service installation succeeded.

Thank you.

Hello Tamas,

    So I tried to select "none" option answering to the question about indices rebuild and didn't find any post-install tasks screen in the console after upgrade completion. Where can I get this tasks list?

Thank you.

Hello,

An upgrade-plan.txt file is created during the upgrade process in <JChem 6.1 home>/cartridge/ folder.

Krisztina

Hello Krisztina,

    I think indices that require manual rebuild step can also be found using the following SQL query (assuming RDBMS is Oracle):

select owner, index_name
from dba_indexes
where lower(ityp_name) = 'jc_idxtype';

    When I tried to rebuild found ones using alter index >idx_name< rebuild parameters('upgradeOnly=y') command I got the following exception (JChem Cartridge server was running):

Error report:
SQL Error: ORA-29858: error occurred in the execution of ODCIINDEXALTER routine
ORA-29540: chemaxon/jchem/cartridge/oraresident/Indexing class doesn't exist
ORA-06512: on  "JCHEM.JCHEM_CORE_PKG", line 114
ORA-06512: on  "JCHEM.JC_IDXTYPE_IM", line 40
29858. 00000 -  "error occurred in the execution of ODCIINDEXALTER routine"
*Cause:    Failed to successfully execute the ODCIIndexAlter routine.
*Action:   Check to see if the routine has been coded correctly.

Thank you.

Hello,

Unfortunately, as we were not able to reproduce the unsuccessful upgrade from version 5.4 to 6.1, and the log files you sent did not provide any helpful information for tracking down the problem, we suggest you to create a new database with the new version of JChem Cartridge - instead of upgrading the old one.

Between these two versions many changes were implemented regarding search processes, calculations, and database specific processes as well, consequently, a successful upgrade would be almost the same as creating new indexes.

Krisztina